package com.intrasoft.nelsonalva.intrasms;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.SQLException;
import java.util.Scanner;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import java.awt.Desktop;
import java.io.File;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class SCQuery {

    public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException, FileNotFoundException, InvalidFormatException {


        System.out.println("**********************************");
        System.out.println("**********[SCQuery v2.2]**********");
        System.out.println("*****------------------------*****");
        System.out.println("*****SC=xxx: afecta SC especifico*");
        System.out.println("*****SC=0: afecta SC activos******");
        System.out.println("*****SC=1: afecta SC CLARO  ******");
        System.out.println("*****SC=2: afecta SC MOVISTAR*****");
        System.out.println("*****SC=3: afecta SC TIGO*********");
        System.out.println("*****------------------------*****");
        System.out.println("**********************************");

        SCQuery scquery = new SCQuery();
        scquery.bufferStrings();
    }

    public void bufferStrings() throws SQLException, ClassNotFoundException, IOException, FileNotFoundException, InvalidFormatException {

        BufferedReader buf = new BufferedReader(new InputStreamReader(System.in));
        Scanner scannerquery = new Scanner(System.in);
        scannerquery.useDelimiter("-");

        System.out.println("---------------------------------|");
        System.out.print("SC: ");
        String sc = buf.readLine();

        SCQuery scquery = new SCQuery();

               
        if (sc.equalsIgnoreCase("open") || sc.equalsIgnoreCase("folder")) {
            
            scquery.openFileUrls(sc);
            System.out.println("Abriendo archivo...");
            scquery.bufferStrings();
        }

        System.out.print("Query: ");

        try {
            scquery.manageQuery(scannerquery, sc);
        } catch (NumberFormatException e) {
            System.out.println("ERROR no existe el SC");
            try {
                scquery.bufferStrings();
            } catch (SQLException e1) {
            }

        }
    }

    public void manageQuery(Scanner scannerquery, String sc) throws SQLException, ClassNotFoundException, IOException, FileNotFoundException, InvalidFormatException {

        SCQuery scquery = new SCQuery();
        String query;

        InputStream inputStream = new FileInputStream(new File("urls.xlsx"));
        Workbook wb = WorkbookFactory.create(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        int rows = sheet.getLastRowNum();
        Row row = sheet.getRow(rows);
        Cell cell = (row.getCell(1));
        cell.setCellType(1);
        int cellint = Integer.parseInt(cell + "");

        int scint = Integer.parseInt(sc);

        SelectQuery sq = new SelectQuery();
        ParseExcel parseexcel = new ParseExcel();

        if (scint > 99 && scint <= cellint) {
            while (scannerquery.hasNext()) {

                query = scannerquery.next();

                if (sq.querySubstring(query)) {
                    launch2(sc, query);
                } else {
                    launch(sc, query);

                    scquery.bufferStrings();
                }
            }
        } else if (scint > 0 && scint <= 9) {

            while (scannerquery.hasNext()) {
                query = scannerquery.next();

                if (sq.querySubstring(query)) {

                    parseexcel.getUrlSc(sc, query);

                } else {

                    parseexcel.getUrl2(sc, query);

                    scquery.bufferStrings();
                }
            }
        } else if (scint == 0) {

            while (scannerquery.hasNext()) {
                query = scannerquery.next();

                if (sq.querySubstring(query)) {

                    parseexcel.getUrlSc2(sc, query);
                } else {

                    parseexcel.getUrl3(sc, query);

                    scquery.bufferStrings();
                }
            }
        } else {
            System.out.println("ERROR no existe el SC");
            try {
                scquery.bufferStrings();
            } catch (SQLException ex1) {
            }

        }
    }

    public void launch(String sc, String query) throws ClassNotFoundException, IOException, FileNotFoundException, InvalidFormatException {

        ParseExcel parseexcel = new ParseExcel();
        String url = parseexcel.getUrl(sc);

        LANConnector lanconnector = new LANConnector();
        int rows;
        try {
            rows = lanconnector.executeQuery(url, query);

            String afectadas = "SC " + sc.substring(0, 3) + ", filas afectadas: " + rows;

            System.out.println(afectadas);
        } catch (SQLException ex) {
            System.out.println("ERROR revisar sintaxis");
        }
    }

    public void launch2(String sc, String query) throws ClassNotFoundException, IOException, FileNotFoundException, InvalidFormatException, SQLException {

        SCQuery scquery = new SCQuery();
        ParseExcel parseexcel = new ParseExcel();
        String url = parseexcel.getUrl(sc);

        LANConnector lanconnector = new LANConnector();

        try {

            lanconnector.executeSelect(url, query, sc);

        } catch (SQLException ex) {
            System.out.println("ERROR revisar sintaxis");
        }
        scquery.bufferStrings();
    }

    public void openFileUrls(String type) {

        try {
            Desktop desktop = null;
            if (Desktop.isDesktopSupported()) {
                desktop = Desktop.getDesktop();
            }

            if (type.equalsIgnoreCase("open")) {
                desktop.open(new File("urls.xlsx")); /*para abrir archivo*/
            } else if (type.equalsIgnoreCase("folder")) {
                desktop.open(new File("Outputs")); /*para abrir carpeta*/
            }
        } catch (IOException ioe) {
        }

    }
}